#!/usr/bin/python
# -*- coding: utf-8 -*-

import psycopg2
import sys
import cgi
import json
import db_conf
import time
import smtplib



def sendMail(pname, receivers, jobid, url):
    initials = pname.split("@")[0]
    sender = 'jake.lin@uni.lu'
    receivers.append("christian.jaeger@uni.lu")
    message = """From: Metabolomics GCMS <christian.jaeger@uni.lu>
To: GCMS User
Subject: Metabobase GCMSJob %s

%s
    """ %(jobid + "_" + initials, "Hello " + pname + ",\n\nGCMSJob successfully created/updated, please print form/sequence tray map, and submit to Christian Jaeger along with samples. \n\nWhen the job is finished and the results are available, you will received a second email. For now, your submission details can be view/update here:\n" + url + "\n\nCheers and feel free to contact Christian/Jake with questions")

    try:
        smtpObj = smtplib.SMTP('localhost')
        smtpObj.sendmail(sender, receivers, message)
    except SMTPException:
        print "Error: unable to send email"

con = None

def post_gc_status(cur, jobkey, status):
    try:
        cur.execute("update gcms_sample_job set gcstatus = '" + status + "' where id = " + jobkey )
    except psycopg2.DatabaseError, e:
        print 'Updating status error %s' % e

def post_new_sample_type(cur, sample_value, sample_name):
    try:
        cur.execute("insert into gcms_sample_types (sample_type, sample_type_option) values ('" + sample_value + "','" + sample_name + "')")
    except psycopg2.DatabaseError, e:
        print 'Adding new sample type error %s' % e

def post_new_method(cur, method_name):
    try:
        cur.execute("insert into gcms_method (method_name) values ('" + method_name + "') RETURNING id")
        return cur.fetchone()[0]	
    except psycopg2.DatabaseError, e:
        print 'Adding new method error %s' % e

def post_new_project(cur, project_name, pi):
    try:
        cur.execute("insert into project (project_name, pi) values ('" + project_name + "'," + pi + ") RETURNING project_id")
        return cur.fetchone()[0]
    except psycopg2.DatabaseError, e:
        print 'Adding new project error %s' % e

def post_experiment(cur, title, experimenter, exp_date, exp_project, keywords, description, ops, eid=0):
    try:
        if (ops == 'insert_gcmsjob'):
    	    cur.execute("""insert into experiment (project_id, title, short_description, description, date, experimenter) values (%s, %s, %s, %s, to_date(%s, 'DD/MM/YYYY'), %s) RETURNING experiment_id""", (exp_project, title, keywords, description, exp_date, experimenter))
        if (ops == 'update_experiment' or ops == 'update_gcmsjob'):
	    cur.execute("""update experiment set project_id=%s, title=%s, short_description=%s,description=%s, date=%s, experimenter=%s where experiment_id = %s RETURNING experiment_id""", (exp_project, title, keywords, description, exp_date, experimenter, eid))
        return cur.fetchone()[0] 
    except psycopg2.DatabaseError, e:
        print 'Adding/updating new experiment error %s' % e

def post_new_groups(cur, grp_name, desc, regex, sample_type_id, strain_id, experiment_id):    
    try:
        #insert into sample_group (sample_group_id, name, description, bio_sample_type_id, strain_id, experiment_id) values (1, 'isogrp_name1', 'desc1', 2, 1, 1);
        cur.execute("""insert into sample_group (name, description, regex, bio_sample_type_id, strain_id, experiment_id) values (%s, %s, %s, %s, %s, %s) RETURNING sample_group_id""", (grp_name, desc, regex, sample_type_id, strain_id, experiment_id)) 
        return cur.fetchone()[0]
    except psycopg2.DatabaseError, e:
        print 'Adding new sample group error %s' % e 

def post_update_groups(cur, grp_name, desc, regex, sample_type_id, strain_id, experiment_id, sample_group_id):
    try:
        #update sample_group set (name, description, regex) = ('exp1', 'ex1 des', 'tag') where sample_group_id = 29;
        cur.execute("""update sample_group set (name, description, regex, bio_sample_type_id, strain_id, experiment_id) = (%s, %s, %s, %s, %s, %s) where sample_group_id = %s  RETURNING sample_group_id""", (grp_name, desc, regex, sample_type_id, strain_id, experiment_id, sample_group_id))
        return cur.fetchone()[0]
    except psycopg2.DatabaseError, e:
        print 'Adding new sample group error %s' % e

#select experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_
#samples, sample_types, jobdate, ssequence, job_meta from gcms_sample_job where id = 12 
def get_gcms_samplejob(cur, job_id):
    try:
        cur.execute("""select experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta from gcms_sample_job where id = """ + job_id ) 
        return cur.fetchone()
    except psycopg2.DatabaseError, e:
        print 'select gcms sample group error %s' % e
        return -1

#select experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_
#samples, sample_types, jobdate, ssequence, job_meta from gcms_sample_job where id = 12 
# gcmsjobid |    operator    | results_date | machinehours | machinerate | addcost |     costnotes      | costtotal |              rawf               |              statf              | additionalf |  filenotes

def get_gcms_results(cur, job_id):
    try:
        cur.execute("""select operator, results_date, machinehours, machinerate, addcost, costnotes, costtotal, rawf, statf, additionalf, filenotes, methodnotes from gcms_job_results where gcmsjobid = """ + job_id + """ order by resultskey desc""")
        return cur.fetchone()
    except psycopg2.DatabaseError, e:
        print 'select gcms sample job results error %s' % e
        return -1


#insert into gcms_sample_job (experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, jobdate, ssequence, job_meta) values (112, 'Metabolomics', 'outcome', 'involvement', 'y', 'n', 'replicate type', 5, '1l', 0, 0, 100, 'AS,A549,BV2', '28/1/2014', 'ssequence', '{meta:"job_meta as json"}' ) RETURNING id;

def post_gcms_samplejob(cur, experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta, job_id=0):
    try:
        if (job_id == 0):
            cur.execute("""insert into gcms_sample_job (experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta) values (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s) RETURNING id""", (experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta))
            return cur.fetchone()[0]
        else:
            cur.execute("""update gcms_sample_job set (experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta) = (%s, %s, %s, %s, %s, %s, %s,%s, %s, %s, %s, %s, %s,%s, %s, %s, %s) where id = %s""", (experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta, job_id))
            return job_id
    except psycopg2.DatabaseError, e:
        print 'Adding new sample group error %s' % e
        return -1

#select experiment_id, labgroup, outcome, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_
#samples, sample_types, jobdate, ssequence, job_meta from gcms_sample_job where id = 12 
def get_experiment_detail(cur, job_id):
    try:
        cur.execute("""select description, short_description, project_id from experiment where experiment_id = """ + job_id )
        return cur.fetchone()
    except psycopg2.DatabaseError, e:
        print 'select experiments  error %s' % e
        return -1

def get_condition_method(cur, job_id):
    try:
        cur.execute("""select method_id, condition from gcms_method_condition where gcmsjobid = """ + job_id)
        return cur.fetchall()
    except psycopg2.DatabaseError, e:
        print 'select condition method error %s' % e
        return -1

def post_condition_method(cur, method, condition, expjob):
    try:
        cur.execute("""insert into gcms_method_condition (method_id, condition, gcmsjobid) values (%s, %s, %s) RETURNING id""", (method, condition, expjob))
	return cur.fetchone()[0]
    except psycopg2.DatabaseError, e:
        print 'Adding new condition method error %s' % e
	return -1

def reset_condition_method(cur, expjob):
    try:
        cur.execute("""delete from gcms_method_condition where gcmsjobid = """ + expjob)
        #Return cur.fetchone()[0]
    except psycopg2.DatabaseError, e:
        print 'Reseting new condition method error %s' % e
        return -1


try:
    qform = cgi.FieldStorage()
    project_name = qform.getvalue('project_name')
    personname = qform.getvalue('personname')
    method = qform.getvalue('method_name')
    title = qform.getvalue('exp_title')
    experimenter = qform.getvalue('exp_people')    
    exp_date = "19/2/2014"#
    exp_date = qform.getvalue('exp_date') 
    keywords = qform.getvalue('exp_keywords')
    description = qform.getvalue('exp_description')
    exp_ops = "get_gcmsjob"#update_gcmsjob"
    exp_ops = qform.getvalue("exp_ops")
    sample_types = qform.getvalue("sample_types")
    conditions = qform.getvalue("conditions")
    active_experiment = qform.getvalue("active_experiment")
    labgroup = qform.getvalue("labgroup")
    outcome = qform.getvalue("outcome") #passed as description
    involvement = qform.getvalue("involvement")
    stat_analysis = qform.getvalue("stat_analysis")
    quan_analysis = qform.getvalue("quan_analysis")
    replicate_type= qform.getvalue("replicate_type")
    replicate_count= qform.getvalue("replicate_count")
    sample_label= qform.getvalue("expsample_label")
    blank_bin= qform.getvalue("blank_bin")
    pool_bin= qform.getvalue("pool_bin")
    total_samples= qform.getvalue("total_samples")
    jobdate= qform.getvalue("job_date")
    ssequence= qform.getvalue("ssequence")
    tag_meta= qform.getvalue("tag_meta")
    job_id= qform.getvalue("job_id")
    if (personname == None):
        personname = ""
    if (tag_meta == None):
        tag_meta = ""
    vial_meta= qform.getvalue("vial_meta")
    if (vial_meta == None):
        vial_meta = ""
    notebook = qform.getvalue("labnotebook")
    if (notebook == None):
	notebook = ""
    email = "jake.lin@uni.lu"#
    email = qform.getvalue("email")
    if (email == None):
         email = ""
    polarity = qform.getvalue("rpolarity")
    if (polarity == None):
         polarity = ""
    extraction_date = qform.getvalue("extraction_date")
    if (extraction_date == None):
         extraction_date = ""
    vialvol = qform.getvalue("vialvol")
    if (vialvol == None):
         vialvol= ""
    internalstandards = qform.getvalue("internalstandards")
    if (internalstandards == None):
         internalstandards= ""
    mediumtype = qform.getvalue("mediumtype")
    if (mediumtype == None):
         mediumtype= ""
    mediumname = qform.getvalue("mediumname")
    if (mediumname == None):
         mediumname= ""
    carbonsources = qform.getvalue("carbonsources")
    if (carbonsources == None):
         carbonsources= ""
    nitrogensource = qform.getvalue("nitrogensource")    
    if (nitrogensource == None):
        nitrogensource = ""
    solveninfo = qform.getvalue("solveninfo")
    if (solveninfo == None):
         solveninfo= ""
    extractenrichment = qform.getvalue("extractenrichment")
    if (extractenrichment == None):
         extractenrichment= ""
    extractcleanup = qform.getvalue("extractcleanup")
    if (extractcleanup == None):
         extractcleanup= ""
    extractconcentration = qform.getvalue("extractconcentration")
    if (extractconcentration == None):
        extractconcentration = ""
    storage = qform.getvalue("storage")
    if (storage == None):
         storage = ""
    job_meta = "personname:" + personname + "&:&tag_meta:"+tag_meta + "&:&vial_meta:" + vial_meta + "&:&labnotebook:" + notebook + "&:&email:" + email + "&:&polarity:" + polarity + "&:&extraction_date:" + extraction_date + "&:&vialvol:" + vialvol + "&:&internalstandards:" + internalstandards + "&:&mediumtype:" + mediumtype + "&:&mediumname:" + mediumname + "&:&carbonsources:" + carbonsources + "&:&nitrogensource:" + nitrogensource + "&:&solveninfo:" + solveninfo + "&:&extractenrichment:" + extractenrichment + "&:&extractcleanup:" + extractcleanup + "&:&extractconcentration:" + extractconcentration + "&:&storage:" + storage   
    config = db_conf.getConfig('metabvis.conf')
    con = psycopg2.connect(host=db_conf.getDBHost(config), database=db_conf.getDBName(config), port=db_conf.getDBPort(config), user=db_conf.getUser(config), password=db_conf.getPassword(config)) 
    cur = con.cursor()
    exp_id = ""
    pa = {}
    job_results = {}
    gcstatus = "200"
    if (exp_ops =="new_project"):
        project_name = qform.getvalue("project_name")
        pi = "5"
	if (qform.getvalue("piid") != None):
	    pi = qform.getvalue("piid")
        pa['_new_proj_id'] = post_new_project(cur, project_name, pi)
    if (exp_ops =="new_sample_type"):
        sv = qform.getvalue("sample_value")
	so = qform.getvalue("sample_name")
        post_new_sample_type(cur, sv, so)
    if (exp_ops == "archive_job"):
        jobkey = qform.getvalue("jobkey")
        cur.execute("""update gcms_sample_job set archived = 1 where id = """ + jobkey)
	gcstatus = "archived"
    if (exp_ops == "unarchive_job"):
        jobkey = qform.getvalue("jobkey")
        cur.execute("""update gcms_sample_job set archived = 0 where id = """ + jobkey)
        gcstatus = "archived"
    if (exp_ops == "update_gcstatus"):
        jobkey = qform.getvalue("jobkey")
        gcstatus = qform.getvalue("gcstatus")
        post_gc_status(cur, jobkey, gcstatus)	 
    if (method != None and method != "" and exp_ops == "new_method"):
        pa['_new_meth_id'] = post_new_method(cur, method)
    if (exp_ops == "get_gcmsjob" and job_id != None):
        gcmsjob_details = get_gcms_samplejob(cur, job_id)
	if (gcmsjob_details != None):
	    pa["experiment_id"] = gcmsjob_details[0]
	    pa["labgroup"] = gcmsjob_details[1]
	    pa["outcome"] = gcmsjob_details[2]
	    pa["involvement"] = gcmsjob_details[3]
	    pa["stat_analysis"] = gcmsjob_details[4]
	    pa["quan_analysis"] = gcmsjob_details[5]
	    pa["replicate_type"] = gcmsjob_details[6]
	    pa["replicate_count"] = gcmsjob_details[7]
	    pa["sample_label"] = gcmsjob_details[8]
	    pa["blank_bin"] = gcmsjob_details[9]
	    pa["pool_bin"] = gcmsjob_details[10]
	    pa["total_samples"] = gcmsjob_details[11]
	    pa["sample_types"] = gcmsjob_details[12]
	    pa["conditions"] = gcmsjob_details[13]
	    pa["jobdate"] = gcmsjob_details[14]
	    pa["ssequence"] = gcmsjob_details[15]
	    pa["job_meta"] = gcmsjob_details[16]
            pa["condition_methods"] = []
	    condition_methods = get_condition_method(cur, job_id)
	    for row in condition_methods:
               pi = []
               for r in range(len(row)):
                    pi.append(row[r])
               pa['condition_methods'].append(pi)
	    edtl = get_experiment_detail(cur, str(pa["experiment_id"]))
            pa["description"] = edtl[0]
            pa["keywords"] = edtl[1]
	    pa["project_id"] = edtl[2]
	    #check to see if results exist -  opeator, results_date, machinehours, machinerate, addcost, costnotes, costtotal, rawf, statf, additionalf, filenotes 
	    gcmsjob_results = get_gcms_results(cur, job_id)
	    if (gcmsjob_results != None):
	        job_results["operator"] = gcmsjob_results[0]
		job_results["results_date"] = gcmsjob_results[1]
		job_results["machinehours"] = gcmsjob_results[2]
		job_results["machinerate"] = gcmsjob_results[3]
		job_results["addcost"] = gcmsjob_results[4]
		job_results["costnotes"] = gcmsjob_results[5]
		job_results["costtotal"] = gcmsjob_results[6]
		job_results["rawf"] = gcmsjob_results[7]
		job_results["statf"] = gcmsjob_results[8]
		job_results["additionalf"] = gcmsjob_results[9]
		job_results["filenotes"] = gcmsjob_results[10]
		job_results["methodnotes"] = gcmsjob_results[11]
	    pa["job_results"] = job_results 
	else:
	    pa['state'] = 'invalid jobid'
    if (exp_ops == "insert_gcmsjob"):
        exp_id = post_experiment(cur, title, experimenter, exp_date, project_name, keywords, description, exp_ops)
	job_id = post_gcms_samplejob(cur, exp_id, labgroup, description, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta)
        #method conditions, medium list as a condition, and alkane
	condition_list = conditions.split(",")
	method_alkane = qform.getvalue("method_alkane")
	medium_alkane = qform.getvalue("medium_alkane")
	method_blank = qform.getvalue("method_blank")
	if (method_alkane != None and (method_alkane != "0" or method_alkane != "-1")):
		rc = post_condition_method(cur, method_alkane, "Alkane", job_id)
	if (medium_alkane != None and (medium_alkane != "0" or medium_alkane != "-1")):
                rc = post_condition_method(cur, medium_alkane, "Medium_Alkane", job_id)		
	if (method_blank != None and (method_blank != "0" or method_blank != "-1")):
                rc = post_condition_method(cur, method_blank, "Blank", job_id)
	for my_condition in condition_list:
            cmeth = qform.getvalue("method_" + my_condition)
            if (cmeth != None and cmeth != ""):
                rc = post_condition_method(cur, cmeth, my_condition, job_id)
	if (sample_types != None and sample_types != ""):
	    sample_list = sample_types.split(",")  
	    for my_sample in sample_list:
                medmeth = qform.getvalue("medium_" + my_sample)
                if (medmeth != None and medmeth != ""):
                    rc = post_condition_method(cur, medmeth, my_sample, job_id)		
	external = qform.getvalue("_source")
	if (external == None):
	    external = ""
	sendMail(personname, ["jlin212@gmail.com", email], "new submission: " + str(job_id), "http://metabolomics.uni.lux/gcms_platform/index.html?jobkey=" + "_" + str(job_id))
        pa['_new_exp_id'] = exp_id
        pa['_new_job_id'] = job_id
    if (exp_ops == "update_gcmsjob"):
        exp_id = active_experiment#"73"#qform.getvalue("active_experiment")
	if (exp_date != None and (exp_date.find("/") != -1)):
	    exp_date = exp_date.split("/")[2] + "-" + exp_date.split("/")[1] + "-" + exp_date.split("/")[0]
	post_experiment(cur, title, experimenter, exp_date, project_name, keywords, description, exp_ops, exp_id)
        post_gcms_samplejob(cur, exp_id, labgroup, description, involvement, stat_analysis, quan_analysis, replicate_type, replicate_count, sample_label, blank_bin, pool_bin, total_samples, sample_types, conditions, jobdate, ssequence, job_meta, job_id)
	
	reset_condition_method(cur, job_id)
        condition_list = conditions.split(",")
        method_alkane = qform.getvalue("method_alkane")
        medium_alkane = qform.getvalue("medium_alkane")
        method_blank = qform.getvalue("method_blank")
        if (method_alkane != None and (method_alkane != "0" or method_alkane != "-1")):
                rc = post_condition_method(cur, method_alkane, "Alkane", job_id)
        if (medium_alkane != None and (medium_alkane != "0" or medium_alkane != "-1")):
                rc = post_condition_method(cur, medium_alkane, "Medium_Alkane", job_id)
        if (method_blank != None and (method_blank != "0" or method_blank != "-1")):
                rc = post_condition_method(cur, method_blank, "Blank", job_id)
        for my_condition in condition_list:
	    cmeth = qform.getvalue("method_" + my_condition)
            if (cmeth != None and cmeth != ""):
                rc = post_condition_method(cur, cmeth, my_condition, job_id)
        if (sample_types != None and sample_types != ""):
            sample_list = sample_types.split(",")
            for my_sample in sample_list:
                medmeth = qform.getvalue("medium_" + my_sample)
                if (medmeth != None and medmeth != ""):
                    rc = post_condition_method(cur, medmeth, my_sample, job_id)
        
        external = qform.getvalue("_source")
        if (external == None):
            external = ""
	sendMail(personname, ["jlin212@gmail.com", email], "Updated: " + str(job_id), "http://metabolomics.uni.lux/gcms_platform/index.html?jobkey=" + "_" + str(job_id))
    #exp_ops = "get_history"
    pa["aaData"] = []
    #status = "200"
    if (exp_ops == "get_history"):
        gckey = qform.getvalue("gckey")
	include_archived = qform.getvalue("in_archived")
	include_karsten = qform.getvalue("in_karsten")
	inkarsten = " and labgroup != 'Karsten Hiller' "
	if (include_karsten == "1"):
	    inkarsten = ""  

	if (gckey == db_conf.getGCKey(config)):
            #cur.execute("select jobdate, gcstatus, labgroup, job_meta, sample_types, outcome, id  from gcms_sample_job where gcstatus != 'DevTest' and lower(job_meta) not like '%jake lin%' order by id asc")
	    reportsql = "select jobdate, gcstatus, labgroup, job_meta, costtotal, outcome, id, archived  from gcms_sample_job g left outer join gcms_job_results gr on (g.id = gr.gcmsjobid) where gcstatus != 'DevTest' and lower(job_meta) not like '%jake lin%' " + inkarsten + "order by labgroup, gcstatus asc"
	    if (include_archived == "0"):
	        reportsql = "select jobdate, gcstatus, labgroup, job_meta, costtotal, outcome, id, archived  from gcms_sample_job g left outer join gcms_job_results gr on (g.id = gr.gcmsjobid) where archived != 1 and gcstatus != 'DevTest' and lower(job_meta) not like '%jake lin%' " + inkarsten + "order by resultskey, labgroup, gcstatus asc"
	    cur.execute(reportsql)
	    rows = cur.fetchall()
	    unique = {}
	    for r in rows:
	        aa = []
	        for i in range(len(r)):
	            ele = r[i]
		    expid = str(r[6])
		    labgroup = r[2]
		    #if (include_karsten == "0" and labgroup == "Karsten Hiller"):
		    #    continue
		    #if (unique.get(expid) != None):
		    #    continue
		    if (i == len(r) - 5):
		        ele = r[i].split("&:&")[0].split(":")[1]
		    if (i == 6):
		        #expid = str(r[i])
			#if (unique.get(expid) == None):
			#unique[expid] = 1
			archived = r[i+1]
		        ele = '<a href="http://metabolomics.uni.lux/gcms_platform/index.html?jobkey=_' + str(r[i]) + '" target="_blank">Details</a>'
			if (archived == 0):
			    ele = ele + '&nbsp;<a onclick="javascript:archive_job(\'' + str(r[i]) + '\')"><font color="blue">Archive</font></a>' 
			else:
			    ele = ele + '&nbsp;<a onclick="javascript:unarchive_job(\'' + str(r[i]) + '\')"><font color="red">UnArchive</font></a>'
		    aa.append(ele)
		if (unique.get(expid) == None):    
	            pa["aaData"].append(aa)
		    unique[expid] = "in"
	else:
	    gcstatus = "pASSword"
    cur.execute("commit")
    pa['_status'] = gcstatus
    print "Content-type: text/html;charset=utf-8\r\n"    
    print json.dumps(pa)
except psycopg2.DatabaseError, e:
    print 'Update Error %s' % e    
    sys.exit(1)    
finally:
    if con:
        con.close()

#if __name__ == "__main__":
#    post_new_credit("new project test")
